package com.dominator.utils.dao;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import com.dominator.utils.system.Pager;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Component;

/**
 * jdbc 工具
 * 
 * @author gsh
 *
 */
@Component
public class JdbcHelper {
	@Resource
	JdbcTemplate jdbcTemplate;
	@Resource
	NamedParameterJdbcTemplate namedJdbcTemplate;

	/**
	 * 标量查询
	 * @param sql
	 * @param params
	 * @return
	 */
	public Double scalar(String sql, Map<String, Object> params) {
		MapSqlParameterSource ps = new MapSqlParameterSource();
		if (params != null && !params.keySet().isEmpty()) {
			ps.addValues(params);
		}
		StringBuilder sb = new StringBuilder();
		sb.append(sql);
		Double res = namedJdbcTemplate.queryForObject(sb.toString(), ps, new RowMapper<Double>() {
			@Override
			public Double mapRow(ResultSet rs, int rowNum) throws SQLException {
				return rs.getDouble(1);
			}
		});
		if(res==null)res = new Double("0.00");
		return res;
	}

	/**
	 * 标题查询
	 * @param sql
	 * @param params
	 * @return
	 */
	public BigDecimal scalarBigDecimal(String sql, Map<String, Object> params) {
		MapSqlParameterSource ps = new MapSqlParameterSource();
		if (params != null && !params.keySet().isEmpty()) {
			ps.addValues(params);
		}
		StringBuilder sb = new StringBuilder();
		sb.append(sql);
		BigDecimal res = namedJdbcTemplate.queryForObject(sb.toString(), ps, new RowMapper<BigDecimal>() {
			@Override
			public BigDecimal mapRow(ResultSet rs, int rowNum) throws SQLException {
				return rs.getBigDecimal(1);
			}
		});
		if(res==null)res = new BigDecimal("0.00");
		return res;
	}

	/**
	 * 总计
	 * @param sqlCount
	 * @param params
	 * @return
	 */
	public Integer count(String sqlCount, Map<String, Object> params) {
		MapSqlParameterSource ps = new MapSqlParameterSource();
		if (params != null && !params.keySet().isEmpty()) {
			ps.addValues(params);
		}
		return namedJdbcTemplate.queryForInt(sqlCount, ps);
	}

	/**
	 * map查询
	 * @param sql
	 * @param params
	 * @return
	 */
	public List<Map<String, Object>> query(String sql, Map<String, Object> params) {
		MapSqlParameterSource ps = new MapSqlParameterSource();
		if (params != null && !params.keySet().isEmpty()) {
			ps.addValues(params);
		}
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sql, ps);
		return list;
	}

	/**
	 * 是否存在
	 * @param sql
	 * @param params
	 * @return
	 */
	public boolean exists(String sql, Map<String, Object> params) {
		StringBuilder sb = new StringBuilder();
		sb.append("select count(0) from (");
		sb.append(sql);
		sb.append(")v limit 1");
		return this.count(sql, params) > 0;
	}

    
	/**
	 * 
	 * @param sql
	 * @param params
	 * @return 影响的行数
	 */
	public Integer execute(String sql, Map<String, Object> params) {
		MapSqlParameterSource ps = new MapSqlParameterSource();
		if (params != null && !params.keySet().isEmpty()) {
			ps.addValues(params);
		}
		Integer res = namedJdbcTemplate.update(sql, ps);
		return res;
	}
	
	/**
	 * 执行sql
	 * @param sql
	 * @param params
	 * @return
	 */
	public void executeNoneQuery(String sql, Map<String, Object> params) {
		MapSqlParameterSource ps = new MapSqlParameterSource();
		if (params != null && !params.keySet().isEmpty()) {
			ps.addValues(params);
		}
		namedJdbcTemplate.execute(sql, ps, new PreparedStatementCallback<Object>() {
			@Override
			public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
				return null;
			}
		});
	}
	
	/**
	 * 分页查询
	 * @param sql
	 * @param params
	 * @return
	 */
	public List<Map<String, Object>> getListPage(Pager pager, String sql) {
		StringBuilder sb = new StringBuilder();
		MapSqlParameterSource ps = new MapSqlParameterSource();
		Integer count = namedJdbcTemplate.queryForInt("select count(0) from (" + sql + ") v", ps);
		sb.append(" limit :start,:size");
		ps.addValue("start", pager.getStart());
		ps.addValue("size", pager.getPageSize());
		List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sql+sb.toString(), ps);
		pager.setRowCount(count);
		return list;
	}
	
	/**
	 * 不分页查询
	 * @param sql
	 * @param params
	 * @return
	 */
	public List<Map<String, Object>> getList(String sql) {
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
		return list;
	}

}
